﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Modellayer;
using System.Data;
using System.Data.SqlClient;
using System.ServiceModel;

namespace Dblayer
{
    public class DbShowRoom
    {

        private SqlCommand cmd = null;

        public DbShowRoom()
        {  
        }

        public void createShowRoom(ShowRoom showRoom)
        {
            try
            {
                Connection.dbconn.Open();
                string insert = @"insert into showroom(showroomnumber, numberofseats, showroomdescription) values ('" + showRoom.showRoomNumber + "','" + showRoom.numberOfSeats + "','" + showRoom.showRoomDescription + "')";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }

        public ShowRoom findShowroom(int showRoomNumber)
        {
            string sql = "SELECT * from showroom WHERE showroomnumber = '" + showRoomNumber + "'";
            return BuildShowRoom(sql);
        }

        private ShowRoom BuildShowRoom(string sql)
        {
            IDataReader dbReader;
            ShowRoom showRoom = null;

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    showRoom = new ShowRoom(int.Parse(dbReader["showroomnumber"].ToString()), int.Parse(dbReader["numberofseats"].ToString()));
                    showRoom.showRoomDescription = (string)dbReader["showroomdescription"];
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find showroom. Fejl: " + e.Message);
                showRoom = null;
                Connection.Close();
            }

            if (showRoom != null)
            {
                return showRoom;
            }
            else
                throw new FaultException("There are no showRoom with that id");
        }

        public ShowRoom findShowroomWithoutDescription(int showRoomNumber)
        {
            string sql = "SELECT * from showroom WHERE showroomnumber = '" + showRoomNumber + "'";
            return BuildShowRoomWithoutDescription(sql);
        }


        private ShowRoom BuildShowRoomWithoutDescription(string sql)
        {
            IDataReader dbReader;
            ShowRoom showRoom = null;

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                if (dbReader.Read())
                {
                    showRoom = new ShowRoom(int.Parse(dbReader["showroomnumber"].ToString()), int.Parse(dbReader["numberofseats"].ToString()));
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find showroom. Fejl: " + e.Message);
                showRoom = null;
                Connection.Close();
            }
            return showRoom;
        }

        public List<ShowRoom> findAllShowRooms()
        {
            string sql = "SELECT * from ShowRoom";
            return BuildAllShowRooms(sql);
        }

        private List<ShowRoom> BuildAllShowRooms(string sql)
        {
            IDataReader dbReader;
            List<ShowRoom> showRooms = new List<ShowRoom>();

            try
            {
                cmd = Connection.GetDBCommand(sql);
                dbReader = cmd.ExecuteReader();
                while (dbReader.Read())
                {
                    ShowRoom showRoom = new ShowRoom(int.Parse(dbReader["showroomnumber"].ToString()), int.Parse(dbReader["numberofseats"].ToString()));
                    showRooms.Add(showRoom);
                }
                Connection.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine("Could not find showroom. Fejl: " + e.Message);
                showRooms = null;
                Connection.Close();
            }
            return showRooms;
        }

        public void updateShowRoom(ShowRoom showRoom)
        {
            try
            {
                Connection.dbconn.Open();

                string insert = @"update showroom set numberOfSeats = '" + showRoom.numberOfSeats + "', showroomdescription = '" + showRoom.showRoomDescription + "' WHERE showroomnumber = '" + showRoom.showRoomNumber + "'";
                cmd = Connection.GetDBCommand(insert);
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("Fejl i opdatering af showroom. Fejl: " + e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.Close();
                }
            }
        }
    }
}